package com.cjc.eas.dao.studentDao;

/**
 * @ClassName StudentDao
 * @Description TODO 学生用户的基本操作
 * @Author fades
 * @Date 2021/5/28 22:44
 * @Version 1.0
 **/

import com.cjc.eas.domain.Course;
import com.cjc.eas.domain.Score;
import com.cjc.eas.domain.Student;
import com.cjc.eas.utils.ConnectDatabaseUtils;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class StudentDao {
    static private Connection conection = null;
    static {
        conection = ConnectDatabaseUtils.getConection();
    }
    /**
      *@Description  学生查询个人信息，根据学号查询
      *@params
      *@return  返回一个student类，再通过该类将各成员变量显示在页面上
      *@author fades 测试通过
      *@since
      */
    public static Student queryStudentInforById(String stuSno){
        Student student = new Student();
        try{
            PreparedStatement statement = conection.prepareStatement("select * from t_student where stuSno = ?");
            statement.setString(1,stuSno);
            try{
                ResultSet resultSet = statement.executeQuery();
                resultSet.next();
                student = transformStu(resultSet);
                return student;
            }catch (Exception ex){
                ex.printStackTrace();
            }
        }catch (SQLException ex){
            ex.printStackTrace();
        }
        return student;
    }

    public static List<Student> getStudents(Student student){

        List<Student> result = new ArrayList<>();
        StringBuffer sql = new StringBuffer("select * from t_student where 1 = 1");
        if(student.getStuName() != null && student.getStuName() != ""){
            sql.append(" and name like '%?%'");
        }
        return result;
    }

    @Test
    public void test1(){
        Student student = queryStudentInforById("1945867001");
        System.out.println(student.getStuName());
    }

    /**
      *@Description 学生查询历年成绩 根据学年和学期查询
      *@params
      *@return 返回一个ArrayList<Score> ，根据改链表将成绩信息显现在页面上
      *@author fades 测试通过
      *@since
      */
    public static ArrayList<Score> queryScore(String scoAnnual,String scoPeriod,String scoSno){
        ArrayList<Score> result = new ArrayList<>();
        try{
            String sql = "select scoCno,couName,scoGetCredits,couType,tchName,couDepartment,scoScore,scoGPA from t_Score,t_Course,t_Teacher where t_Course.couWno=t_Teacher.tchWno and t_Score.scoCno=t_Course.couCno and scoAnnual=? and scoPeriod=? and scoSno=?";
            PreparedStatement ps = conection.prepareStatement(sql);
            ps.setString(1,scoAnnual);
            ps.setString(2,scoPeriod);
            ps.setString(3,scoSno);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                String scoCno = rs.getString("scoCno");
                String scoName = rs.getString("couName");
                int  scoGetCredits = rs.getInt("scoGetCredits");
                String scoType = rs.getString("couType");
                String scoTeacher = rs.getString("tchName");
                String scoDepartment = rs.getString("couDepartment");
                int scoScore = rs.getInt("scoScore");
                float scoGPA = rs.getFloat("scoGPA");
                Score score = new Score();
                score.setScoCno(scoCno);
                score.setScoName(scoName);
                score.setScoGetCredits(scoGetCredits);
                score.setScoType(scoType);
                score.setScoTeacher(scoTeacher);
                score.setScoDepartment(scoDepartment);
                score.setScoScore(scoScore);
                score.setScoGPA(scoGPA);
                result.add(score);
            }

        }catch(SQLException e){
            e.printStackTrace();
        }
        return  result;
    }

    @Test
    public void test2(){
        ArrayList<Score> scores = queryScore("2020-2021","第二学期","1945867001");
        for(int i=0 ;i<scores.size();i++){
            System.out.println(scores.get(i).getScoCno()+"   "+scores.get(i).getScoName()+"   "+scores.get(i).getScoScore());
        }

    }
    /**
      *@Description 学生选课功能
      *@params
      *@return
      *@author fades 测试通过
      *@since
      */
    public static void SelectCourseBycouCno(List<Course> courseList, String couSno){
        for(int i=0;i< courseList.size();i++){
            try{
                String sql = "insert into t_Score(scoCno,scoSno) values (?,?)";
                PreparedStatement ps = conection.prepareStatement(sql);
                ps.setString(1,courseList.get(i).getCouCno());
                ps.setString(2,couSno);
                ps.executeUpdate();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
    }
    @Test
    public void test3(){
        Course course1 = new Course();
        course1.setCouCno("bk001");
        course1.setCouName("计算机组成原理");
        course1.setCouType("理工类");
        course1.setCouWno("t1001");
        course1.setCouDepartment("软件学院");
        Course course2 = new Course();
        course2.setCouCno("bk002");
        course2.setCouName("程序设计基础");
        course2.setCouType("理工类");
        course2.setCouWno("t1002");
        course2.setCouDepartment("软件学院");
        ArrayList<Course> coursesList = new ArrayList<>();
        coursesList.add(course1);
        coursesList.add(course2);
        SelectCourseBycouCno(coursesList,"1945867005");
    }

    /**
     *@Description TODO 将结果集封装到Student类中
     *@params
     *@return Student对象
     *@author fades
     *@since
     */

    public static Student transformStu(ResultSet resultSet) throws Exception{
        Student student =new Student();
        student.setStuSno(resultSet.getString("stuSno"));
        student.setStuName(resultSet.getString("stuName"));
        student.setStuGender(resultSet.getString("stuGender"));
        student.setStuNation(resultSet.getString("stuNation"));
        student.setStuDateOfBirth(resultSet.getString("stuDateOfBirth"));
        student.setStuID(resultSet.getString("stuID"));
        student.setStuClassNum(resultSet.getString("stuClassNum"));
        student.setStuDepartment(resultSet.getString("stuDepartment"));
        student.setStuContactNum(resultSet.getString("stuContactNum"));
        student.setStuContactAddr(resultSet.getString("stuContactAddr"));
        student.setStuContactPerson(resultSet.getString("stuContactPerson"));
        student.setStuEnrollmentYear(resultSet.getString("stuEnrollmentYear"));
        student.setStuEduLevel(resultSet.getString("stuEduLevel"));
        student.setStuMajor(resultSet.getString("stuMajor"));
        student.setStuLengthOfStudy(resultSet.getString("stuLengthOfStudy"));
        student.setStuPostcode(resultSet.getString("stuPostcode"));
        student.setStuNativePlace(resultSet.getString("stuNativePlace"));
        student.setStuNativePlaceType(resultSet.getString("stuNativePlaceType"));
        student.setStuEmail(resultSet.getString("stuEmail"));
        student.setStuAccount(resultSet.getString("stuAccount"));
        student.setStuPasswd(resultSet.getString("stuPasswd"));
        return student;
    }

    /**
      *@Description 该方法为StuFrame_Btn3_1页面服务
      *@params
      *@return
      *@author fades
      *@since
      */
    public static  ArrayList<Course> showCourselist(){
        ArrayList<Course> result = new ArrayList<>();
        try{
            String sql = "select couCno,couName,couType,tchName,couDepartment,couTime from t_Teacher,t_Course where t_Teacher.tchWno=t_Course.couWno";
            PreparedStatement ps = conection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                Course course = new Course();
                String couCon = rs.getString("couCno");
                String couName = rs.getString("couName");
                String couType = rs.getString("couType");
                String tchName = rs.getString("tchName");
                String couDepartment = rs.getString("couDepartment");
                String couTime = rs.getString("couTime");
                course.setCouCno(couCon);
                course.setCouName(couName);
                course.setCouType(couType);
                course.setCouTeacher(tchName);
                course.setCouDepartment(couDepartment);
                course.setCouTime(couTime);
                result.add(course);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return  result;
    }
    @Test
    public void test4(){
        ArrayList<Course> courseArrayList = showCourselist();
        for(int i=0;i<courseArrayList.size();i++){
            System.out.println(courseArrayList.get(i).getCouCno()+" "+courseArrayList.get(i).getCouName()+" "+courseArrayList.get(i).getCouTeacher());
        }
    }
}